I work for a company that sells software. And SOME people, because of their untrusing nature, need to be convinced that the software that we want to sell them actually works. This process, in some large, bureaucratic institutions, involves salesmen and conference calls and schmoozing and all of those things, that I, as a technical guy, like to leave to the more sociable ranks in the company.

Part of the process of demonstrating that the software that we produce meets certain functional criteria may involve webcasts or meetings where we show the product, in all it’s glory, working flawlessly. This means having a server always available for this purpose and in tip-top working order.

The fly in the ointment is this: how to allow non-technical people to run software on this machine and potentially modify (a polite way of saying BREAK) the SQL Server, its’ databases, and operating environment, and then later in the day allow someone else to run the same demonstartions on a server that’s restored to its’ original environment. I know what you’re thinking and this was my first though as well: virtual server! You could create a virtual server and roll back the entire environment to an earlier state without a lot of trouble.

The virtual server idea was shot dead like a wounded racehorse because we need to demonstrate the increased performance gains of our SQL Backup software: we needed a high-performance environment with the quickest disks, more RAM than a virtual environment can provide, and ultra-quick GUI response time. What we needed was SQL Server with a rewind button so any atrocities committed against the server environment could be rolled back at will, or better yet on a schedule so I don’t have to tinker around with the server ever again.

Ideally, I thought, leveraging backup technology would work. Assuming that all of the SQL Server configuration is kept in the system databases, it would ‘simply’ be a matter of taking a one-time, incorruptable backup of these databases and restoring it back to the platform.

So I backed up all of the databases on the demo server and saved the backups to an out of the way path (NOT the default SQL Server backup path!!!). The backup file names for all demo databases also correspond to the database names, making it easier to automate the restore job.

But since you need to restart SQL multiple times and restore system databases, a SQL Script is impractical for this task. I need to work outside the SQL Server and for that reason I implemented rewind as a VBScript. The first thing a coder needs is a plan, so I wrote out my strategy as pseudo-code:

1. If SQL Server is started, stop it and all dependent services
2. Start SQL Server in single-user mode
3. Restore the master database
4. Shut down the server and bring it up in multi-user mode
5. Restore model and msdb
6. Restore all user databases (the list of databases is in our newly-restored master database)
7. Start SQL Server and all dependent services
8. Delete all backup files from the default backup location to prevent demonstrators from filling up the disk with SQL Backup demo data

This script has been running as a Windows Scheduled Task at midnight for about six months, and has saved a lot of time maintaining a SQL Server 2005 demonstration environment for sales and marketing concerns. There is only one basic flaw and that is that if the SQL Server software is updated, for instance service packs and Microsoft hotfixes, the system databases need to be backed up again, or the system database restores will fail.